package foodmaterial.service.impl;

import foodmaterial.config.DataSourceContextHolder;
import foodmaterial.config.DataSourceUtils;
import foodmaterial.config.DynamicDataSource;
import foodmaterial.dao.FoodMaterialDao;
import foodmaterial.dao.InitDao;
import foodmaterial.dao.PermissionDao;
import foodmaterial.dao.PurchaseDao;
import foodmaterial.dto.*;
import foodmaterial.entity.*;
import foodmaterial.service.InitService;
import foodmaterial.utils.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory ;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.io.*;
import java.nio.file.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
@EnableScheduling
public class InitServiceImpl extends BaseService implements InitService {

    private static final Logger LOGGER = LoggerFactory.getLogger(InitServiceImpl.class) ;

    @Value("${spring.datasource.username}")
    private String user;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverName;
    @Value("${spring.datasource.url}")
    private String url ;

    @Autowired
    private InitDao dao ;

    @Autowired
    private PermissionDao permissionDao;

    @Autowired
    private FoodMaterialDao foodMaterialDao ;

    @Autowired
    private PurchaseDao purchaseDao ;

    @Autowired
    private DynamicDataSource dynamicDataSource ;

    private void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void close(Connection con,Statement statement,ResultSet rs) {
        closeResultSet(rs);
        closeStatement(statement);
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private List<String> data(String fileName) throws IOException {
        InputStreamReader fis = null;
        InputStream in = null ;
        BufferedReader br = null;
        List<String> sql = new ArrayList<>() ;
        try {
            in = this.getClass().getClassLoader().getResourceAsStream(fileName) ;
            fis = new InputStreamReader(in);
            br = new BufferedReader(fis);
            String temp = null;
            StringBuilder sb = new StringBuilder() ;
            while ((temp = br.readLine()) != null) {
                if (temp.length() == 0 || temp.charAt(0) == '#') continue;
                sb.append(temp) ;
                if (temp.charAt(temp.length() - 1) == ';') {
                    sb.deleteCharAt(sb.length() - 1) ;
                    sql.add(sb.toString()) ;
                    sb.setLength(0);
                }
            }
        } finally {
            FileTools.close(br);
            FileTools.close(fis);
            FileTools.close(in);
        }
        return sql ;
    }

    private String init(String db,long id) throws ClassNotFoundException, SQLException, IOException {
        Connection con = null ;
        Statement statement = null ;
        PreparedStatement ps = null ;
        ResultSet rs = null ;
        try {
            Class.forName(driverName);
            con = DriverManager.getConnection(url, user, password);
            statement = con.createStatement();
            StringBuilder sql = new StringBuilder();
            sql.append("select COUNT(*) from information_schema.schemata where schema_name='")
               .append(db).append("'");
            rs = statement.executeQuery(sql.toString()) ;
            if (rs.next()) {
                if (rs.getInt(1) == 1) return db + "数据库已经存在了";
            }
            closeResultSet(rs) ;
            sql.setLength(0);
            sql.append("CREATE DATABASE ").append(db);
            statement.addBatch(sql.toString());
            sql.setLength(0);
            sql.append("USE ").append(db);
            statement.addBatch(sql.toString());
            List<String> list = data("sql.sql") ;
            for (String s : list) {
                statement.addBatch(s);
            }
            sql.setLength(0);
            sql.append("insert into sys_user_role_relation(id,role_id,user_id) values (1,1,");
            sql.append(id).append(')');
            statement.addBatch(sql.toString());
            statement.executeBatch();
            sql.setLength(0);
            sql.append("insert into sys_user(id,account,name,password,last_login_time,self_enabled,");
            sql.append("self_account_non_expired,self_account_non_locked,self_credentials_non_expired ,");
            sql.append("create_time,update_time,creator,update_user,user_type,db) values(");
            sql.append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)") ;
            ps = con.prepareStatement(sql.toString()) ;
            ps.setLong(1,id);
            ps.setString(2,db);
            ps.setString(3,"系统管理员");
            ps.setString(4,PasswordUtil.encodePassword(Constants.DEFAULT_PASSWORD));
            Date date = new Date();
            ps.setDate(5,new java.sql.Date(date.getTime()));
            ps.setBoolean(6,true);
            ps.setBoolean(7,true);
            ps.setBoolean(8,true);
            ps.setBoolean(9,true);
            ps.setDate(10,new java.sql.Date(date.getTime()));
            ps.setDate(11,new java.sql.Date(date.getTime()));
            ps.setString(12,"系统");
            ps.setString(13,"系统");
            ps.setString(14,Constants.USER_ADMIN);
            ps.setString(15,db);
            ps.execute() ;
        } finally {
            close(null,ps,null) ;
            close(con,statement,rs) ;
        }
        return null ;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public String addCompanyDatabase(CompanyDatabase companyDatabase) throws SQLException, IOException, ClassNotFoundException {
        long id = nextId() ;
        String result = init(companyDatabase.getDatabase(),id) ;
        if (result != null) return result ;
        companyDatabase.setId(nextId());
        int rows = dao.insertCompanyDatabase(companyDatabase);
        if (rows > 0) return null ;
        return "新增数据库实例失败" ;
    }

    @Override
    public int modifyCompanyDatabase(CompanyDatabase companyDatabase) {
        return dao.updateCompanyDatabase(companyDatabase);
    }

    @Override
    public List<CompanyDatabase> queryCompanyDatabase() {
        return dao.selectCompanyDatabase();
    }

    @Override
    public int modifySysParams(SysParams sysParams) {
        return dao.updateSysParams(sysParams);
    }

    @Override
    public List<SysParams> querySysParams() {
        return dao.selectSysParams();
    }

    @Override
    public SysParams querySysParamsByCode(String code) {
        return dao.selectSysParamsByCode(code);
    }

    @Override
    public List<SysParams> querySysParamsByCodes(List<String> where) {
        return dao.selectSysParamsByCodes(where);
    }

    @Override
    public Integer getCompanyUseDays() {
        SysUser userDetails = (SysUser) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        if (userDetails.getDb() != null) {
            CompanyDatabase cd = dao.selectCompanyDatabaseByDb(userDetails.getDb()) ;
            if (cd == null) return null ;
            Date termOfValidity = cd.getTermOfValidity();
            if (termOfValidity != null) {
                long end = termOfValidity.getTime();
                long current = DateUtil.parse(DateUtil.format(new Date(),Constants.DEFAULT_DATE_FORMAT)).getTime();
                int days = (int) ((end - current) / 1000 / 60 / 60 / 24);
                return days;
            }
        }
        return null;
    }

    @Override
    public String queryCompanyFun() {
        List<SysPermission> result = dao.selectCompanyFun();
        if (result == null || result.isEmpty()) return "" ;
        StringBuilder sb = new StringBuilder() ;
        for (SysPermission sp : result) {
            sb.append(sp.getId()).append(",") ;
        }
        sb.deleteCharAt(sb.length() - 1) ;
        return sb.toString() ;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public String initCompanyPermission(PermissionDto dto) {
        int rows = permissionDao.deletePermission();
        if (rows <= 0) {
            for (SysRole sr : dto.getRole()) {
                sr.setDescription("");
            }
            for (RolePermissionRelation rp : dto.getRolePermission()) {
                rp.setId(nextId());
            }
            dao.insertDefaultRole(dto.getRole());
            dao.insertDefaultRolePermission(dto.getRolePermission()) ;
        }
        List<SysPermission> sysPermissions = new ArrayList<>();
        for (int i = 0; i < dto.getId().size(); i++) {
            SysPermission sp = new SysPermission();
            sp.setId(dto.getId().get(i));
            sp.setName(dto.getName().get(i));
            sp.setCode(dto.getCode().get(i));
            sp.setDescription(dto.getDescription().get(i));
            sysPermissions.add(sp);
        }
        rows = permissionDao.insertPermission(sysPermissions);
        if (rows <= 0) return "生成公司可使用权限失败";
        return null;
    }

    private OriginalProcessRateDto getOriginalProcessRateDto(int count) {
        FoodMaterialDto dto = new FoodMaterialDto() ;
        dto.setOriginalProcess(Boolean.TRUE);
        dto.setStop(Boolean.FALSE);
        dto.setProcessPurchase(Boolean.FALSE);
        List<FoodMaterial> list = foodMaterialDao.selectFoodMaterial(dto) ;
        if (list == null || list.isEmpty()) return null ;
        OriginalProcessRateDto dto1 = new OriginalProcessRateDto() ;
        dto1.setProcessIds(list);
        dto1.setLimitAmount(count);
        return dto1 ;
    }

    void recentlyOriginalProcessRate(int count) {
        try {
            OriginalProcessRateDto dto = getOriginalProcessRateDto(count);
            if (dto == null) return;
            List<FoodMaterial> list = dao.selectRecentlyOriginalProcessRate(dto);
            if (list == null || list.isEmpty()) return;
            foodMaterialDao.updateOriginalProcessRate(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    void randomOriginalProcessRate(int count) {
        try {
            OriginalProcessRateDto dto = getOriginalProcessRateDto(count);
            if (dto == null) return;
            List<FoodMaterial> list = dao.selectRandomOriginalProcessRate(dto);
            if (list == null || list.isEmpty()) return;
            foodMaterialDao.updateOriginalProcessRate(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private boolean pathExist(Path path) {
        return Files.exists(path, new LinkOption[]{ LinkOption.NOFOLLOW_LINKS});
    }

    /**
     * 清理公司不使用的图片
     * @param db
     */
    void cleanImage(String db) {
        StringBuilder sb = new StringBuilder() ;
        String credentialsPath = dao.selectSysParamsByCode(Constants.CREDENTIALS_PATH).getParamValue();
        credentialsPath += db ;

        Path path = Paths.get(credentialsPath) ;
        if (pathExist(path)) {
            try (DirectoryStream<Path> directoryStream = Files.newDirectoryStream(path)) {
                SupplierDto supplierDto = new SupplierDto();
                supplierDto.setEnabled(Boolean.TRUE);
                List<Supplier> list = purchaseDao.selectSupplier(supplierDto);
                List<String> credentialsImage = new ArrayList<>();
                List<String> supplierImage = new ArrayList<>();
                for (Path pathTemp : directoryStream) {
                    credentialsImage.add(pathTemp.getFileName().toString());
                }
                for (Supplier supplier : list) {
                    if (StringUtils.isEmpty(supplier.getCredentials()) == false)
                        supplierImage.add(supplier.getCredentials());
                }
                credentialsImage.removeAll(supplierImage);
                for (String file : credentialsImage) {
                    sb.setLength(0);
                    sb.append(credentialsPath).append('/').append(file);
                    path = Paths.get(sb.toString());
                    Files.deleteIfExists(path);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        String foodPath = dao.selectSysParamsByCode(Constants.FOOD_PATH).getParamValue();
        foodPath += db ;
        path = Paths.get(foodPath) ;
        if (pathExist(path)) {
            try (DirectoryStream<Path> directoryStream = Files.newDirectoryStream(path)) {
                FoodMaterialDto dto = new FoodMaterialDto();
                dto.setStop(Boolean.FALSE);
                dto.setPic("pic");
                List<FoodMaterial> list = foodMaterialDao.selectFoodMaterial(dto);
                List<String> foodImage = new ArrayList<>();
                List<String> foodMaterialImage = new ArrayList<>();
                for (Path pathTemp : directoryStream) {
                    foodImage.add(pathTemp.getFileName().toString());
                }
                for (FoodMaterial food : list) {
                    if (StringUtils.isEmpty(food.getPic()) == false)
                        foodMaterialImage.add(food.getPic());
                }
                foodImage.removeAll(foodMaterialImage);
                for (String file : foodImage) {
                    sb.setLength(0);
                    sb.append(foodPath).append('/').append(file);
                    path = Paths.get(sb.toString());
                    Files.deleteIfExists(path);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        String testPath = dao.selectSysParamsByCode(Constants.TEST_REPORT_PATH).getParamValue();
        testPath += db ;
        path = Paths.get(testPath) ;
        if (pathExist(path)) {
            try (DirectoryStream<Path> directoryStream = Files.newDirectoryStream(path)) {
                TestReportDto dto = new TestReportDto();
                List<TestReport> list = foodMaterialDao.selectTestReport(dto);
                List<String> testImage = new ArrayList<>();
                List<String> testReportImage = new ArrayList<>();
                for (Path pathTemp : directoryStream) {
                    testImage.add(pathTemp.getFileName().toString());
                }
                for (TestReport tr : list) {
                    testReportImage.add(tr.getReportPic());
                }
                testImage.removeAll(testReportImage);
                for (String file : testImage) {
                    sb.setLength(0);
                    sb.append(foodPath).append('/').append(file);
                    path = Paths.get(sb.toString());
                    Files.deleteIfExists(path);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Scheduled(fixedDelay=5000 * 6)
    public void dynamicSetDataSource() {
        List<CompanyDatabase> list = queryCompanyDatabase() ;
        LOGGER.debug("服务进行开通设置处理。。。");
        for (CompanyDatabase db :list) {
            if (dynamicDataSource.isExistDataSource(db.getDatabase())) {
                //服务被停止使用，那么直接把用户的数据库服务下线
                if (db.getEnabled() == false) DataSourceUtils.removeDataSource(db.getName());
                continue;
            }
            db.setDriverName(driverName);
            db.setUsername(user);
            db.setPassword(password);
            DataSourceUtils.addDataSource(db.getDatabase(),DataSourceUtils.createDataSource(db));
            FoodLock.getInstance().addLock(db.getDatabase());
        }
    }

    @Scheduled(cron = "0 0 0 * * ?")
    public void calculateOriginalProcessRate() {
        LOGGER.debug("服务开始计算毛净转化率。。。");
        List<CompanyDatabase> databases = queryCompanyDatabase() ;
        for (CompanyDatabase database : databases) {
            try {
                DataSourceContextHolder.setDBType(database.getDatabase());
                cleanImage(database.getDatabase()) ;
                CompanySysParams params = permissionDao.selectSysParamsByCode("0003") ;
                String val[] = params.getParamValue().split("-") ;
                if ("1".equals(val[0])) continue;
                if ("2".equals(val[0])) recentlyOriginalProcessRate(Integer.parseInt(val[1])) ;
                else if ("3".equals(val[0])) randomOriginalProcessRate(Integer.parseInt(val[1])) ;
            } finally {
                DataSourceContextHolder.clearDBType();
            }
        }
        LOGGER.debug("服务计算毛净转化率结束。。。");
    }

}
